Using SQL WHERE Clause With Comparison Operators

SQL Tutorial Home

In this lesson we cover the types of conditions you can put in the WHERE clause of any SQL SELECT query. There are several combinations based on the comparison operator and the data type of the columns in the operation.

Here is the example table. Suppose you work on a farm growing apples. The database contains a single table named apple with all the production history. The table has the following schema:

Year Apple Variety Number of Trees in Production Tons Produced Harvest day Price per Ton First summer storm
2020 Red Delicious 2000 102 06/23/2020 54.50 07/03/2020
2020 Magic Green 700 33 06/12/2020 62.60 07/03/2020
2019 Red Delicious 1800 87 07/15/2019 52.25 07/12/2019
2019 Magic Green 500 26 06/28/2019 59.40 07/12/2019
2018 Red Delicious 1800 92 07/02/2018 56.75 06/03/2018
2017 Red Delicious 1500 76.5 07/18/2017 51.45 07/30/2017
2016 Red Delicious 1500 72 06/26/2016 47.60 06/23/2016

Simple Comparison in SQL

SQL has several comparison operators. The most simple one is the equal operator. You can compare any data type column using the equal operator. For example:


SELECT apple_variety, tons_produced
FROM   apples
WHERE  year = 2019

After executing the SQL the database returns the records where the year column is 2019:

apple_variety tons_produced
Red Delicious 87
Magic Green 26

Where equal is the most used comparison operator, SQL offers many other interesting comparison operators, including:
GREATER THAN >
LESS THAN <
GREATER THAN OR EQUAL >=
LESS THAN OR EQUAL. <=
NOT EQUAL <> or !=

For example if you want to obtain the records in the range between 2017 and 2019 you can use the following query:


SELECT apple_variety, number_of_tress_in_production, tons_produced
FROM   apples
WHERE  year >= 2017 AND year <=2019

After executing the SQL the database returns this data result set:

apple_variety number_of_tress_in_production tons_produced
Red Delicious 1800 87
Magic Green 500 26
Red Delicious 1800 92
Red Delicious 1500 76.5

The range comparison can be applied to other data types like text strings or date columns. For example, to obtain the years where harvest started before the first summer storm:


SELECT year, 
       apple_variety,
       number_of_tress_in_production, 
       tons_produced
FROM   apples
WHERE  harvest_day < first_summer_storm

The result is:

year apple_variety number_of_tress_in_production tons_produced
2020 Red Delicious 2000 102
2020 Magic Green 700 33
2019 Magic Green 500 26
2017 Red Delicious 1500 76.5

You can also use the inequality operator with numeric, date or text string data type columns to exclude some records. In this example, you obtain all history production, excluding the apple variety Red Delicious.


SELECT year, apple_variety, number_of_trees_in_production,   
       tons_produced, harvest_day, price_per_ton
FROM   apples
WHERE  apple_variety <> ‘Red Delicious’
year apple_variety number_of_trees_in_production tons_produced harvest_day price_per_ton
2020 Magic Green 700 33 06/12/2020 62.60
2019 Magic Green 500 26 06/28/2019 59.40

SQL WHERE with Arithmetic Operations and Tranformations

SQL allows arithmetic calculations in the WHERE conditions. This type of condition increases the expressive power of SQL. For example, suppose you want to obtain those years where production was more than 50 kilograms of apples per tree. You can put an arithmetic operation to calculate the kilograms of apples produced per tree:


SELECT year, apple_variety, number_of_trees_in_production,   
       tons_produced, 
       (tons_produced * 1000 ) / number_of_trees_in_production AS kilograms_per_tree
FROM   apples
WHERE  (tons_produced * 1000 ) / number_of_trees_in_production > 50

The results:

year apple_variety number_of_trees_in_production tons_produced kilograms_per_tree
2020 Red Delicious 2000 102 51
2020 Magic Green 700 33 47.14
2019 Red Delicious 1800 87 48.33
2019 Magic Green 500 26 52
2018 Red Delicious 1800 92 51.11
2017 Red Delicious 1500 76.5 51
2016 Red Delicious 1500 72 48

You can create both conditions with arithmetic operations and conditions involving transformations to the values of the columns using text string operators, date operators or numeric operators. For example, you want to extract the month component from the harvest_day column to obtain the years harvest started in June. The EXTRACT function used in this query is dialect-specific (PostgreSQL) SQL dialect.


SELECT year, apple_variety, harvest_day
FROM   apples
WHERE  EXTRACT (month FROM harvest_day) = 6

Instead of doing an arithmetic calculation, you are doing a transformation. In other words, you are transforming a date into a month. Next, you compare it against June (where 6 is a numeric representation for June). The query results:

year apple_variety harvest_day
2020 Red Delicious 06/23/2020
2020 Magic Green 06/12/2020
2019 Magic Green 06/28/2019
2016 Red Delicious 06/26/2016

Closing Words

In this Lesson, we covered several comparison operators you can use in the WHERE clause. We also explained how to create a WHERE condition using an arithmetic operation or a value transformation.

In the next lesson of this tutorial, we cover logical operators like AND, OR and NOT that you can use to create more complex WHERE clauses. Keep going, Learn SQL and increase your skills!

IN THIS PAGE